﻿import json
from datetime import datetime, date

from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError
from config import DB_URL

engine = create_engine(DB_URL)
weather_table_structure = {
    'table_name': 'suzhou',
    'columns': [
        {
            'name': 'Date',
            'type': 'DATE',
            'attributes': ['PRIMARY KEY']
        },
        {
            'name': 'MaxTemperature',
            'type': 'INT',
            'attributes': []
        },
        {
            'name': 'MinTemperature',
            'type': 'INT',
            'attributes': []
        },
        {
            'name': 'WeatherCondition',
            'type': 'VARCHAR(50)',
            'attributes': []
        }
    ]
}


def get_weather(city):
    get_sql = f'SELECT * FROM {city}'

    try:
        with engine.connect() as conn:
            result = conn.execute(text(get_sql))
            rows = result.fetchall()

            # 获取列名
            column_names = [column['name'] for column in weather_table_structure['columns']]

            # 将结果转换为字典列表，并确保日期被转换为字符串
            weather_list = []
            for row in rows:
                weather_dict = dict(zip(column_names, row))
                for key, value in weather_dict.items():
                    if isinstance(value, (datetime, date)):
                        weather_dict[key] = value.isoformat()
                weather_list.append(weather_dict)

            # 将结果转换为 JSON 字符串
            weather_json = json.dumps(weather_list, indent=4)
            data = json.loads(weather_json)
            print(data)
            return data

    except SQLAlchemyError as e:
        print(f"An error occurred: {e}")


# 假设 engine 已经被定义，例如：
# engine = create_engine('mysql+pymysql://user:password@localhost/your_database')

def insert_weather(city, date, max_temperature, min_temperature, weather_condition):
    table_name = city
    insert_sql = text(f'''
        INSERT INTO {table_name} (Date, MaxTemperature, MinTemperature, WeatherCondition)
        VALUES (:date, :max_temperature, :min_temperature, :weather_condition)
    ''')
    try:
        with engine.connect() as conn:
            conn.execute(insert_sql, {
                'date': date,
                'max_temperature': max_temperature,
                'min_temperature': min_temperature,
                'weather_condition': weather_condition
            })
            conn.commit()
            print('Weather data inserted successfully')
    except SQLAlchemyError as e:
        print(f'An error occurred while inserting the weather data: {e}')


# insert_weather('hefei', '2023-10-01', 25, 15, 'Sunny')


def calculate_average_temperature(weather_list):
    average_temperatures = []
    highest_temp = None  # 用来记录最高气温
    lowest_temp = None   # 用来记录最低气温

    # 遍历每一条天气记录
    for weather in weather_list:
        # 获取最高气温和最低气温
        max_temp = weather.get('MaxTemperature')
        min_temp = weather.get('MinTemperature')

        if max_temp is not None and min_temp is not None:
            # 计算每日平均气温
            avg_temp = (max_temp + min_temp) / 2
            weather['AvgTemperature'] = avg_temp  # 添加到记录中
            average_temperatures.append(weather)  # 将记录添加到返回的列表中

            # 记录最高温度和最低温度
            if highest_temp is None or max_temp > highest_temp:
                highest_temp = max_temp
            if lowest_temp is None or min_temp < lowest_temp:
                lowest_temp = min_temp
        else:
            print(f"缺少温度数据，无法计算平均气温: {weather}")

    # 计算所有记录的平均气温总和
    total_avg_temperature = 0
    for i in average_temperatures:
        temp = i.get('AvgTemperature')
        if temp is not None:
            total_avg_temperature += temp  # 累加每一条记录的平均气温

    # 计算所有记录的平均气温
    if len(average_temperatures) > 0:
        overall_avg_temperature = total_avg_temperature / len(average_temperatures)
    else:
        overall_avg_temperature = None  # 如果没有数据，则返回 None

    return average_temperatures, overall_avg_temperature, highest_temp, lowest_temp



def get_temperature_level(avg_temp):
    if avg_temp >= 35:
        return '高温'
    elif avg_temp >= 25:
        return '正常'
    else:
        return '低温'





